HW#2 --- last modified January 01 1970 00:00:00..

Solution set.

Due date: Oct 2

Files to be submitted:
  problems.doc
  partition.java
  output.txt

Purpose: To experiment with and use some of the database design techniques of Chapter 16.

Specification:

For 1.-4. below put all of your answers in problems.doc. Be aware that the current limit on submitted file size is 1.5MB.

1. For each of the following domains of a Library database come up with an ER diagram then perform a view integration of the domains: Film Archives, Branch Locations, Reservations.

2. Map the ER Diagram to relations and suggest in the resulting tables which should have indexes on them and what kind of indexes they should be.

3. Consider the following schema for movies:
Movie(movieId, movieName, distributor, year, mediaType, acquireDate, cost)
The most common queries on this table are to look up how much a movie costs based on its ID (which is scanned) and to find out when a new release will be in stores based on its name. Suggest a vertical partitioning of the above table.

4. For each of the following queries suggest ways to tune it to improve its performance. Assume the tables have following row counts Ship has 1000 rows, Container has 10000000 rows. Assume there is a clustering index on value.

a. SELECT DISTINCT Container.TrackNo
FROM Container, Ship
WHERE Container.shipID=Ship.ID AND
(Container.value > 100000 OR Container.value < 500000)

b. Assume we have the following view as well
CREATE VIEW Ship_Container AS
   SELECT * from Ship, Container;

SELECT TrackNo
FROM Ship_Container
WHERE 0.5*value > SELECT AVG(value)
   FROM Ship_Container;

c. SELECT TrackNo
FROM Container
WHERE NOT ( value = SELECT MAX(Container.value)
FROM Container);

d. SELECT Ship.name
FROM Ship, Container
WHERE Ship.ID = Container.shipID AND Ship.name LIKE 'Sea%' AND
Container.value =1234;

5. To test out your new Oracle accounts write a Java program partition.java that uses JDBC to connect to an Oracle database consisting of the original table Movie from problem 3 together with the tables from your suggested vertical partitioning. It then times how long the most common queries takes on the original table versus the vertical partitioning. To make the test reasonable, insert 1000 randomly generated rows into each of these tables. The file output.txt should have the output from a test run of your program.

Point Breakdown